package sy.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import sy.pageModel.DataGrid;
import sy.pageModel.OrderDoctor;
import sy.pageModel.OrderDrug;
import sy.service.OrderDrugServiceI;
import sy.util.DateTimeUtility;
import sy.util.DateUtil;
import sy.util.StringUtil;

@Service("orderDrugService")
public class OrderDrugServiceImpl extends BaseService implements OrderDrugServiceI {

	
	@Override
	public DataGrid datagrid(OrderDrug order) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from xj_be_doctor where yyj='2'";
		sql = addWhere(order, sql);
		System.out.println(sql);
		long total = template.queryForLong(sql);
		System.out.println(total);
		dg.setTotal(total);
		
		sql = "select id, did, vnumber, name, state, phone, date_time, sex, id_card, birthday, sccj, distributor, sq_time, is_urgent, ys_money, sj_money, comments, need_invoice, need_dis "
		      + "from xj_be_doctor where yyj='2'";
		sql = addWhere(order, sql);
		sql = addOrderBy(order, sql);
		sql = addLimit(order, sql);
		System.out.println(sql);
		final List<OrderDrug> orderList = new ArrayList<OrderDrug>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	OrderDrug order = new OrderDrug();
                order.setId(rs.getString("id"));
                order.setOrderId(rs.getString("did"));
                order.setCardId(rs.getString("vnumber"));
                order.setName(rs.getString("name"));
                order.setStatus(rs.getString("state"));
                order.setMobile(rs.getString("phone"));
                order.setCreateTime(rs.getString("date_time"));
                order.setGender(rs.getString("sex"));
                order.setCid(rs.getString("id_card"));
                order.setBornDate(rs.getString("birthday"));
                order.setFactory(rs.getString("sccj"));
                order.setDealer(rs.getString("distributor"));
                order.setServiceTime(rs.getString("sq_time"));
                order.setIsUrgent(rs.getString("is_urgent"));
                order.setEstimateFee(rs.getDouble("ys_money"));
                order.setActualFee(rs.getDouble("sj_money"));
                order.setServiceDetails(rs.getString("comments"));
                order.setIsNeedInvoice(rs.getString("need_invoice"));
                order.setIsNeedDeliver(rs.getString("need_dis"));
            	orderList.add(order);
            }});
		dg.setRows(orderList);		
		return dg;
	}
	
	@Override
	public DataGrid datagridForAccepted(OrderDrug order) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from xj_be_doctor where yyj='2' and state='" + OrderDoctor.ORDER_STATUS_ID_ACCEPTED + "'";
		sql = addWhere(order, sql);
		System.out.println(sql);
		long total = template.queryForLong(sql);
		System.out.println(total);
		dg.setTotal(total);
		
		sql = "select id, did, vnumber, name, state, phone, date_time, sex, id_card, birthday, sccj, distributor, sq_time, is_urgent, ys_money, sj_money, comments, need_invoice, need_dis "
		      + "from xj_be_doctor where yyj='2' and state='" + OrderDoctor.ORDER_STATUS_ID_ACCEPTED + "'";
		sql = addWhere(order, sql);
		sql = addOrderBy(order, sql);
		sql = addLimit(order, sql);
		System.out.println(sql);
		final List<OrderDrug> orderList = new ArrayList<OrderDrug>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	OrderDrug order = new OrderDrug();
                order.setId(rs.getString("id"));
                order.setOrderId(rs.getString("did"));
                order.setCardId(rs.getString("vnumber"));
                order.setName(rs.getString("name"));
                order.setStatus(rs.getString("state"));
                order.setMobile(rs.getString("phone"));
                order.setCreateTime(rs.getString("date_time"));
                order.setGender(rs.getString("sex"));
                order.setCid(rs.getString("id_card"));
                order.setBornDate(rs.getString("birthday"));
                order.setFactory(rs.getString("sccj"));
                order.setDealer(rs.getString("distributor"));
                order.setServiceTime(rs.getString("sq_time"));
                order.setIsUrgent(rs.getString("is_urgent"));
                order.setEstimateFee(rs.getDouble("ys_money"));
                order.setActualFee(rs.getDouble("sj_money"));
                order.setServiceDetails(rs.getString("comments"));
                order.setIsNeedInvoice(rs.getString("need_invoice"));
                order.setIsNeedDeliver(rs.getString("need_dis"));
            	orderList.add(order);
            }});
		dg.setRows(orderList);		
		return dg;
	}
	
	private String addWhere(OrderDrug order, String sql) {
		if(!StringUtil.isNullOrEmpty(order.getOrderId())) {
			sql += " and did like '%" + order.getOrderId() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCardId())) {
			sql += " and vnumber like '%" + order.getCardId() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getName())) {
			sql += " and name like '%" + order.getName() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getMobile())) {
			sql += " and phone like '%" + order.getMobile() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCreateTimeStart())) {
			sql += " and date_time >= '" + order.getCreateTimeStart() + "'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCreateTimeEnd())) {
			sql += " and date_time <= '" + order.getCreateTimeEnd() + "'";
		}
		return sql;
	}
	
	private String addLimit(OrderDrug order, String sql) {
		int fromRow = (order.getPage() - 1) * order.getRows();
	    sql += " limit " + fromRow + "," + order.getRows();
	    return sql;
	}
	
	private String addOrderBy(OrderDrug order, String sql) {
		if (order.getSort() != null) {
			if("cardId".equals(order.getSort())) {
				order.setSort("vnumber");
			} else if("name".equals(order.getSort())) {
				order.setSort("convert(name using gbk)");
			} else if("createTime".equals(order.getSort())) {
				order.setSort("date_time");
			} else if("mobile".equals(order.getSort())) {
				order.setSort("phone");
			} else if("orderId".equals(order.getSort())) {
				order.setSort("did");
			} else if("statusName".equals(order.getSort())) {
				order.setSort("state");
			} else if("bornDate".equals(order.getSort())) {
				order.setSort("birthday");
			} else if("genderName".equals(order.getSort())) {
				order.setSort("sex");
			} else if("cid".equals(order.getSort())) {
				order.setSort("id_card");
			} else if("factory".equals(order.getSort())) {
				order.setSort("sccj");
			} else if("dealer".equals(order.getSort())) {
				order.setSort("distributor");
			} else if("serviceTime".equals(order.getSort())) {
				order.setSort("sq_time");
			} else if("isUrgentName".equals(order.getSort())) {
				order.setSort("is_urgent");
			} else if("isNeedInvoiceName".equals(order.getSort())) {
				order.setSort("need_invoice");
			} else if("isNeedDeliverName".equals(order.getSort())) {
				order.setSort("need_dis");
			}
			sql += " order by " + order.getSort() + " " + order.getOrder();
		}
		return sql;
	}
	
	@Override
	@Transactional
	public OrderDrug add(OrderDrug order) throws Exception {
		System.out.println(order);
		//得到urgent的天数
		int urgentDays = this.getUrgentDays("2");
		String isUrgent = DateTimeUtility.isXWorkingDaysEarlier(order.getServiceTime(), urgentDays) ? "N" : "Y";
	    order.setIsUrgent(isUrgent);
	    Date now = new Date();
	    order.setCreateTime(DateUtil.format(now));
	    order.setId(java.util.UUID.randomUUID().toString());
	    order.setOrderId("Y-" + order.getCardId() + "-" + DateUtil.format(now, "yyyyMMddHHmmss"));
		
		String sql = "insert into xj_be_doctor (id,name,sex,birthday,vnumber,id_card,sccj,distributor,sq_time,state,date_time,yyj,did,is_urgent,phone,need_invoice,need_dis) " + 
		             "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		Object[] paras = new Object[] {
				  order.getId(),
				  order.getName(),
				  order.getGender(),
				  order.getBornDate(),
				  order.getCardId(),
				  order.getCid(),
				  order.getFactory(),
				  order.getDealer(),
				  order.getServiceTime(),
				  OrderDoctor.ORDER_STATUS_ID_WAIT_ACCEPT,
				  order.getCreateTime(),
				  "2",
				  order.getOrderId(),
				  order.getIsUrgent(),
				  order.getMobile(),
				  order.getIsNeedInvoice(),
				  order.getIsNeedDeliver()
		};
		
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加订单失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加订单失败，请联系系统管理员！");
		}
		
	    System.out.println(order);
	    
	    //添加药品信息
	    String[] drugItemArr = order.getDrugItems().split(";");
	    for(String item : drugItemArr) {
	    	String[] fieldArr = item.split(",");
	    	sql = "insert into xj_be_drug (id,be_id,name,specifications,jl,count,brief) values(?,?,?,?,?,?,?)";
	    	paras = new Object[] {
					  java.util.UUID.randomUUID().toString(),
					  order.getOrderId(),
					  fieldArr[0],
					  fieldArr[1],
					  fieldArr[2],
					  fieldArr[3],
					  fieldArr.length == 5 ? fieldArr[4] : ""
	    	};
	    	affectRow = 0;
			try {
			    affectRow = template.update(sql, paras);
			} catch(Exception e) {
				e.printStackTrace();
				throw new RuntimeException("添加订单失败，请联系系统管理员！");
			}
			if(affectRow == 0) {
				throw new RuntimeException("添加订单失败，请联系系统管理员！");
			}
	    }
	    return order;
	}
	
	@Override
	@Transactional
	public OrderDrug edit(OrderDrug order) throws Exception {
		int affectRow = 0;
		String sql = "update xj_be_doctor set sccj=?,distributor=?,ys_money=?,sj_money=?,comments=?,state=?,need_invoice=?,need_dis=?" 
		             + " where id=?";
		System.out.println(sql);
		order.setStatus(orderStatus(order.getStatus()));
		Object[] paras = new Object[] {
		    order.getFactory(),
		    order.getDealer(),
		    order.getEstimateFee(),
		    order.getActualFee(),
		    order.getServiceDetails(),
		    order.getStatus(),
		    order.getIsNeedInvoice(),
		    order.getIsNeedDeliver(),
		    order.getId()
		};
		editOrderBySql(sql,paras);
		updateMenber(order.getCardId(),order.getOrderId(),order.getActualFee(),order.getStatus());
		return order;
	}
	
	@Override
	@Transactional
	public OrderDrug addDetails(OrderDrug order) throws Exception {
		int affectRow = 0;
		String sql = "update xj_be_doctor set ys_money=?,sj_money=?,comments=?,state=?" 
		             + " where id=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
		    order.getEstimateFee(),
		    order.getActualFee(),
		    order.getServiceDetails(),
		    order.getStatus(),
		    order.getId()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("生成详单失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("订单记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条订单记录，生成详单失败，请联系系统管理员！");
		}
		return order;
	}

	@Override
	@Transactional
	public OrderDrug editDrugSave(OrderDrug order) {
		String sql = "update xj_be_doctor set sccj=?,distributor=?,ys_money=?,sj_money=?,comments=?,need_invoice=?,need_dis=? where id=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
				order.getFactory(),
				order.getDealer(),
				order.getEstimateFee(),
				order.getActualFee(),
				order.getServiceDetails(),
				order.getIsNeedInvoice(),
				order.getIsNeedDeliver(),
				order.getId()
		};
		editOrderBySql(sql,paras);
		return order;
	}

	@Override
	@Transactional
	public OrderDrug editOrderStatus(OrderDrug order) {
		String sql = "update xj_be_doctor set state=? where id=?";
		order.setStatus(OrderDoctor.ORDER_STATUS_ID_CANCELLED);
		Object[] paras = new Object[] {
				order.getStatus(),
				order.getId()
		};
		editOrderBySql(sql,paras);
		return order;
	}

	private int getUrgentDays(String typeId) {
		String sql = "select c_day from xj_subscribe where typeid=?";
		System.out.println(sql);
		int result = template.queryForInt(sql, new Object[]{typeId});
		System.out.println(result);
		return result;
	}
	
	@Override
	public OrderDrug getDrugItemsByOrderId(String orderId) throws Exception {
		String sql = "select name,specifications,jl,count,brief from xj_be_drug where be_id=?";
		System.out.println(sql);
		OrderDrug order = new OrderDrug();
		order.setOrderId(orderId);
		Object[] paras = new Object[] {
			    order.getOrderId()
		};
		final List<String[]> itemList = new ArrayList<String[]>();
        template.query(sql, paras, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	String[] fields = new String[5];
            	fields[0] = rs.getString("name");
            	fields[1] = rs.getString("specifications");
            	fields[2] = rs.getString("jl");
            	fields[3] = rs.getString("count");
            	fields[4] = rs.getString("brief");
            	itemList.add(fields);
            }});
        StringBuffer sb = new StringBuffer();
        for(String[] fields : itemList) {
        	String temp = "";
        	for(String field : fields) {
        		temp += "," + field;
        	}
        	sb.append(";" + temp.substring(1));
        }
        order.setDrugItems(sb.substring(1));
        System.out.println(order.getDrugItems());
		return order;
	}

}
